import tkinter as tk
from tkinter import ttk, messagebox
import mysql.connector
import pandas as pd
from PIL import Image, ImageTk

# 数据库连接
def connect_db():
    return mysql.connector.connect (
        host="localhost",
        user="root",
        password="123456",
        database="student_m"
    )




# 注册
def register():
    def register_user():
        username = reg_username_entry.get()
        password = reg_password_entry.get()

        conn = connect_db()
        cursor = conn.cursor()
        query = "INSERT INTO users (username, password) VALUES (%s, %s)"
        cursor.execute(query, (username, password))
        conn.commit()
        conn.close()

        messagebox.showinfo("注册成功", "用户已注册成功")
        reg_window.destroy()

    reg_window = tk.Tk()
    reg_window.title("注册")

    tk.Label(reg_window, text="用户名").grid(row=0, column=0, padx=10, pady=5)
    tk.Label(reg_window, text="密码").grid(row=1, column=0, padx=10, pady=5)

    reg_username_entry = tk.Entry(reg_window)
    reg_password_entry = tk.Entry(reg_window, show="*")

    reg_username_entry.grid(row=0, column=1, padx=10, pady=5)
    reg_password_entry.grid(row=1, column=1, padx=10, pady=5)

    tk.Button(reg_window, text="注册", command=register_user).grid(row=2, column=1, padx=10, pady=5)

    center_window(reg_window)
    reg_window.mainloop()


# 加载并设置背景图片
def load_and_set_background_image(window, img_path="image.jpg"):
    # 获取窗口的宽度和高度
    width = window.winfo_screenwidth()
    height = window.winfo_screenheight()

    # 加载并调整图像大小
    img = Image.open(img_path)
    img = img.resize((width, height))  # 调整图像大小以适应窗口
    photo = ImageTk.PhotoImage(img)

    # 创建标签并设置背景图像
    background_label = tk.Label(window, image=photo)
    background_label.image = photo  # 保持对图片的引用，避免被垃圾回收
    background_label.place(relwidth=1, relheight=1)  # 让标签填满整个窗口


# 登录验证
def login():
    username = username_entry.get()
    password = password_entry.get()

    conn = connect_db()
    cursor = conn.cursor()
    query = "SELECT * FROM users WHERE username = %s AND password = %s"
    cursor.execute(query, (username, password))
    user = cursor.fetchone()
    conn.close()

    if user:
        messagebox.showinfo("登录成功", "欢迎！")
        root.destroy()
        main_menu()
    else:
        messagebox.showerror("登录失败", "用户名或密码错误")


# 主菜单
def main_menu():
    menu = tk.Tk()
    menu.title("学生信息管理系统 - 主菜单")

    # 调整窗口大小
    width = 400
    height = 200
    screen_width = menu.winfo_screenwidth()
    screen_height = menu.winfo_screenheight()
    x = (screen_width - width) // 2
    y = (screen_height - height) // 2
    menu.geometry('{}x{}+{}+{}'.format(width, height, x, y))

    load_and_set_background_image(menu)


    tk.Button(menu, text="学生信息管理", command=student_info_management).pack(pady=10)
    tk.Button(menu, text="学生成绩管理", command=student_score_management).pack(pady=10)
    tk.Button(menu, text="学生成绩统计", command=student_score_statistics).pack(pady=10)
    tk.Button(menu, text="学生信息查询", command=search_student_info).pack(pady=10)

    menu.mainloop()


# 显示数据表格
def show_table(data, window, title, table_name):
    window.title(title)
    window.geometry('1000x600')  # 调整窗口大小
    frame = ttk.Frame(window)
    frame.pack(fill=tk.BOTH, expand=True)

    # 创建水平和垂直滚动条
    x_scrollbar = ttk.Scrollbar(frame, orient="horizontal")
    y_scrollbar = ttk.Scrollbar(frame, orient="vertical")
    x_scrollbar.pack(side=tk.BOTTOM, fill=tk.X)
    y_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

    tree = ttk.Treeview(frame, xscrollcommand=x_scrollbar.set, yscrollcommand=y_scrollbar.set)
    tree.pack(fill=tk.BOTH, expand=True)

    x_scrollbar.config(command=tree.xview)
    y_scrollbar.config(command=tree.yview)

    # 定义列
    tree["column"] = list(data.columns)
    tree["show"] = "headings"

    # 设置列标题
    for col in tree["column"]:
        tree.heading(col, text=col)

    # 插入数据
    for index, row in data.iterrows():
        tree.insert("", "end", values=list(row))

    if table_name:
        def add_row():
            add_window = tk.Tk()
            add_window.title("增加数据")

            entries = []
            for i, col in enumerate(data.columns):
                tk.Label(add_window, text=col).grid(row=i, column=0)
                entry = tk.Entry(add_window)
                entry.grid(row=i, column=1)
                entries.append(entry)

            def save_changes():
                new_values = [entry.get() for entry in entries]
                if table_name == "s_infor":
                    insert_infor_table(new_values, tree)
                elif table_name == "s_score":
                    insert_score_table(new_values, tree)
                add_window.destroy()

            tk.Button(add_window, text="保存", command=save_changes).grid(row=len(data.columns), column=0)

        def manage_row():
            selected_item = tree.selection()[0]
            values = tree.item(selected_item, "values")
            manage_window = tk.Tk()
            manage_window.title("管理数据")

            entries = []
            for i, col in enumerate(data.columns):
                tk.Label(manage_window, text=col).grid(row=i, column=0)
                entry = tk.Entry(manage_window)
                entry.grid(row=i, column=1)
                entry.insert(0, values[i])
                entries.append(entry)

            def save_changes():
                new_values = [entry.get() for entry in entries]
                if table_name == "s_infor":
                    update_infor_table(selected_item, new_values, tree)
                elif table_name == "s_score":
                    update_score_table(selected_item, new_values, tree)
                manage_window.destroy()

            tk.Button(manage_window, text="保存修改", command=save_changes).grid(row=len(data.columns), column=0)

        def delete_row():
            selected_item = tree.selection()[0]
            values = tree.item(selected_item, "values")
            if table_name == "s_infor":
                delete_infor_table(values[0])
            elif table_name == "s_score":
                delete_score_table(values[0])
            tree.delete(selected_item)

        add_button = tk.Button(window, text="增加", command=add_row)
        add_button.pack(side=tk.LEFT, padx=10, pady=10)

        manage_button = tk.Button(window, text="修改", command=manage_row)
        manage_button.pack(side=tk.LEFT, padx=10, pady=10)

        delete_button = tk.Button(window, text="删除", command=delete_row)
        delete_button.pack(side=tk.LEFT, padx=10, pady=10)


def update_infor_table(selected_item, new_values, tree):
    conn = connect_db()
    cursor = conn.cursor()
    query = "UPDATE s_infor SET 学号 = %s, 姓名 = %s, 性别 = %s, 班级 = %s, 专业 = %s, 学院 = %s, 联系方式 = %s WHERE 学号 = %s"
    cursor.execute(query, (*new_values, new_values[0]))
    conn.commit()
    conn.close()
    tree.item(selected_item, values=new_values)


def update_score_table(selected_item, new_values, tree):
    conn = connect_db()
    cursor = conn.cursor()
    query = "UPDATE s_score SET 学号 = %s, 姓名 = %s, C语言 = %s, python = %s, Linux = %s WHERE 学号 = %s"
    cursor.execute(query, (*new_values, new_values[0]))
    conn.commit()
    conn.close()
    tree.item(selected_item, values=new_values)


def insert_infor_table(new_values, tree):
    conn = connect_db()
    cursor = conn.cursor()
    query = "INSERT INTO s_infor (学号, 姓名, 性别, 班级, 专业, 学院, 联系方式) VALUES (%s, %s, %s, %s, %s, %s, %s)"
    cursor.execute(query, new_values)
    conn.commit()
    conn.close()
    tree.insert("", "end", values=new_values)


def insert_score_table(new_values, tree):
    conn = connect_db()
    cursor = conn.cursor()
    query = "INSERT INTO s_score (学号, 姓名, C语言, python, Linux, 班级) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(query, new_values)
    conn.commit()
    conn.close()
    tree.insert("", "end", values=new_values)


def delete_infor_table(id):
    conn = connect_db()
    cursor = conn.cursor()
    query = "DELETE FROM s_infor WHERE 学号 = %s"
    cursor.execute(query, (id,))
    conn.commit()
    conn.close()


def delete_score_table(id):
    conn = connect_db()
    cursor = conn.cursor()
    query = "DELETE FROM s_score WHERE 学号 = %s"
    cursor.execute(query, (id,))
    conn.commit()
    conn.close()


# 学生信息管理
def student_info_management():
    def load_data():
        conn = connect_db()
        query = "SELECT * FROM s_infor"
        data = pd.read_sql(query, conn)
        conn.close()
        return data

    info_window = tk.Tk()

    data = load_data()
    show_table(data, info_window, "学生信息管理", "s_infor")
    center_window(info_window)
    info_window.mainloop()


# 学生成绩管理
def student_score_management():
    def load_data():
        conn = connect_db()
        query = "SELECT * FROM s_score"
        data = pd.read_sql(query, conn)
        conn.close()
        return data

    score_window = tk.Tk()
    data = load_data()
    show_table(data, score_window, "学生成绩管理", "s_score")
    center_window(score_window)
    score_window.mainloop()


# 学生成绩统计
def student_score_statistics():
    def load_data():
        conn = connect_db()
        query = """
        SELECT s_infor.*, s_score.*
        FROM s_infor
        JOIN s_score ON s_infor.学号 = s_score.学号
        """
        data = pd.read_sql(query, conn)
        conn.close()
        return data

    def load_data1():
        conn = connect_db()
        query = """
        SELECT C语言, python, Linux
        FROM s_score
        """
        data1 = pd.read_sql(query, conn)
        conn.close()
        return data1

# 优秀学生查询
    def query_excellent_student():
        def show_excellent_students(class_name):
            conn = connect_db()
            query = """
            SELECT s_infor.学号, s_infor.姓名, (s_score.C语言 + s_score.python + s_score.Linux) / 3 AS average_score
            FROM s_infor
            JOIN s_score ON s_infor.学号 = s_score.学号
            WHERE s_score.班级 = %s
            """
            data = pd.read_sql(query, conn, params=(class_name,))
            conn.close()

            # 筛选平均分大于80分的学生
            excellent_students = data[data['average_score'] > 80]

            if excellent_students.empty:
                messagebox.showinfo("优秀学生", "未找到该班级的优秀学生")
            else:
                info_window = tk.Toplevel()
                show_table(excellent_students, info_window, "优秀学生信息", None)
                center_window(info_window)

        # 弹出输入班级的窗口
        class_input_window = tk.Toplevel()
        class_input_window.title("输入班级")

        tk.Label(class_input_window, text="请输入班级：").grid(row=0, column=0, padx=10, pady=5)
        class_entry = tk.Entry(class_input_window)
        class_entry.grid(row=0, column=1, padx=10, pady=5)

        search_button = tk.Button(class_input_window, text="查询",
                                  command=lambda: show_excellent_students(class_entry.get()))
        search_button.grid(row=1, column=0, columnspan=2, padx=10, pady=10)

        center_window(class_input_window)

    # 班级平均成绩查询
    def query_class_average():
        stats_window = tk.Tk()
        stats_window.title("成绩统计")

        frame = ttk.Frame(stats_window)
        frame.pack(fill=tk.BOTH, expand=True)

        class_label = tk.Label(frame, text="请输入班级：")
        class_label.grid(row=0, column=0, padx=10, pady=5)
        class_entry = tk.Entry(frame)
        class_entry.grid(row=0, column=1, padx=10, pady=5)

        course_label = tk.Label(frame, text="请输入课程：")
        course_label.grid(row=1, column=0, padx=10, pady=5)
        course_entry = tk.Entry(frame)
        course_entry.grid(row=1, column=1, padx=10, pady=5)

        def calculate_average():
            class_name = class_entry.get()
            course = course_entry.get()

            conn = connect_db()
            query = f"SELECT AVG({course}) FROM s_score WHERE 班级 = %s"
            average_score = pd.read_sql(query, conn, params=(class_name,))
            conn.close()

            if average_score.empty:
                messagebox.showinfo("班级平均成绩", f"未找到班级为{class_name}的{course}课程成绩")
            else:
                messagebox.showinfo("班级平均成绩",
                                    f"{class_name}班级的{course}课程平均成绩为: {average_score.iloc[0, 0]:.2f}")

        search_button = tk.Button(frame, text="查询班级平均成绩", command=calculate_average)
        search_button.grid(row=2, column=0, columnspan=2, padx=10, pady=10)

        center_window(stats_window)

        stats_window.mainloop()

    # 分数区间人数统计
    def score_statistics():
        def calculate_statistics():
            class_name = class_entry.get()
            course = course_entry.get()
            conn = connect_db()
            query = f"SELECT {course} FROM s_score WHERE 班级 = %s"
            scores = pd.read_sql(query, conn, params=(class_name,))
            conn.close()

            # 统计分数范围
            score_ranges = {
                '0-59': scores[scores[course] < 60].count()[0],
                '60-79': scores[(scores[course] >= 60) & (scores[course] < 80)].count()[0],
                '80-89': scores[(scores[course] >= 80) & (scores[course] < 90)].count()[0],
                '90-100': scores[scores[course] >= 90].count()[0]
            }

            # 显示统计结果
            stats_window = tk.Toplevel()
            stats_window.title("分数统计")

            row_num = 0
            for key, value in score_ranges.items():
                label_text = f"{key}: {value} 人"
                label = tk.Label(stats_window, text=label_text)
                label.grid(row=row_num, column=0, sticky="w")
                row_num += 1

            center_window(stats_window)

        stats_input_window = tk.Toplevel()
        stats_input_window.title("输入班级和课程")

        class_label = tk.Label(stats_input_window, text="请输入班级：")
        class_label.grid(row=0, column=0, padx=10, pady=5)
        class_entry = tk.Entry(stats_input_window)
        class_entry.grid(row=0, column=1, padx=10, pady=5)

        course_label = tk.Label(stats_input_window, text="请输入课程：")
        course_label.grid(row=1, column=0, padx=10, pady=5)
        course_entry = tk.Entry(stats_input_window)
        course_entry.grid(row=1, column=1, padx=10, pady=5)

        search_button = tk.Button(stats_input_window, text="统计", command=calculate_statistics)
        search_button.grid(row=2, column=0, columnspan=2, padx=10, pady=10)

        center_window(stats_input_window)

    def query_course_rank():
        def show_course_rank(class_name, course):
            conn = connect_db()
            query = f"""
            SELECT 学号, 姓名, {course}
            FROM s_score
            WHERE 班级 = %s
            ORDER BY {course} DESC
            """
            data = pd.read_sql(query, conn, params=(class_name,))
            conn.close()

            if data.empty:
                messagebox.showinfo("课程成绩排名", f"未找到班级为{class_name}的{course}课程成绩")
            else:
                info_window = tk.Toplevel()
                show_table(data, info_window, f"{course}课程成绩排名", None)
                center_window(info_window)

        class_input_window = tk.Toplevel()
        class_input_window.title("输入班级和课程")

        class_label = tk.Label(class_input_window, text="请输入班级：")
        class_label.grid(row=0, column=0, padx=10, pady=5)
        class_entry = tk.Entry(class_input_window)
        class_entry.grid(row=0, column=1, padx=10, pady=5)

        course_label = tk.Label(class_input_window, text="请输入课程：")
        course_label.grid(row=1, column=0, padx=10, pady=5)
        course_entry = tk.Entry(class_input_window)
        course_entry.grid(row=1, column=1, padx=10, pady=5)

        search_button = tk.Button(class_input_window, text="查询",
                                  command=lambda: show_course_rank(class_entry.get(), course_entry.get()))
        search_button.grid(row=2, column=0, columnspan=2, padx=10, pady=10)

        center_window(class_input_window)

    def query_class_rank():
        def show_class_rank(class_name):
            conn = connect_db()
            query = """
            SELECT 学号, 姓名, (C语言 + python + Linux) / 3 AS average_score
            FROM s_score
            WHERE 班级 = %s
            ORDER BY average_score DESC
            """
            data = pd.read_sql(query, conn, params=(class_name,))
            conn.close()

            if data.empty:
                messagebox.showinfo("班级平均成绩排名", f"未找到班级为{class_name}的学生信息")
            else:
                info_window = tk.Toplevel()
                show_table(data, info_window, f"{class_name}班级学生平均分排名", None)
                center_window(info_window)

        class_input_window = tk.Toplevel()
        class_input_window.title("输入班级")

        tk.Label(class_input_window, text="请输入班级：").grid(row=0, column=0, padx=10, pady=5)
        class_entry = tk.Entry(class_input_window)
        class_entry.grid(row=0, column=1, padx=10, pady=5)

        search_button = tk.Button(class_input_window, text="查询",
                                  command=lambda: show_class_rank(class_entry.get()))
        search_button.grid(row=1, column=0, columnspan=2, padx=10, pady=10)

        center_window(class_input_window)



    def compute_statistics():
        data = load_data()
        data1 = load_data1()
        stats_window = tk.Tk()
        stats_window.title("成绩统计")

        frame = ttk.Frame(stats_window)
        frame.pack(fill=tk.BOTH, expand=True)

        # 班级平均成绩查询
        search_button = tk.Button(frame, text="班级平均成绩", command=query_class_average)
        search_button.grid(row=0, column=0,  padx=10, pady=10)

        # 查询优秀学生
        excellent_button = tk.Button(frame, text="优秀学生", command=query_excellent_student)
        excellent_button.grid(row=0, column=1, padx=10, pady=10)

        # 分数区间人数统计
        stats_button = tk.Button(frame, text="分数区间统计", command=score_statistics)
        stats_button.grid(row=0, column=2, padx=10,  pady=10)

        # 课程成绩排名
        rank_button = tk.Button(frame, text="课程成绩排名", command=query_course_rank)
        rank_button.grid(row=0, column=3, padx=10, pady=10)

        #班级平均成绩排名
        rank_button = tk.Button(frame, text="平均成绩排名", command=query_class_rank)
        rank_button.grid(row=0, column=4, padx=10, pady=10)


        # 设置窗口居中
        center_window(stats_window)

        stats_window.mainloop()

    def show_statistics(data1, title):
        stats_window = tk.Toplevel()
        stats_window.title(title)

        frame = ttk.Frame(stats_window)
        frame.pack(fill=tk.BOTH, expand=True)

        tree = ttk.Treeview(frame)
        tree.pack(fill=tk.BOTH, expand=True)

        tree["column"] = list(data1.index)
        tree["show"] = "headings"

        for col in tree["column"]:
            tree.heading(col, text=col)

        tree.insert("", "end", values=list(data1))

        # 设置窗口居中
        center_window(stats_window)

    stat_window = tk.Tk()
    data = load_data()
    show_table(data, stat_window, "学生成绩统计", None)

    stats_button = tk.Button(stat_window, text="统计", command=compute_statistics)
    stats_button.pack(side=tk.BOTTOM, pady=10)

    # 设置窗口居中
    center_window(stat_window)

    stat_window.mainloop()

#  学生信息查询
def search_student_info():
    def query_student_info():
        student_id = student_id_entry.get()

        conn = connect_db()
        query = "SELECT * FROM s_infor WHERE 学号 = %s"
        data = pd.read_sql(query, conn, params=(student_id,))
        conn.close()

        if data.empty:
            messagebox.showerror("查询失败", "未找到该学生信息")
        else:
            info_window = tk.Toplevel()
            show_table(data, info_window, "学生信息查询结果", None)
            center_window(info_window)

    search_window = tk.Toplevel()
    search_window.title("学生信息查询")

    tk.Label(search_window, text="请输入学号：").grid(row=0, column=0, padx=10, pady=5)
    student_id_entry = tk.Entry(search_window)
    student_id_entry.grid(row=0, column=1, padx=10, pady=5)

    search_button = tk.Button(search_window, text="查询", command=query_student_info)
    search_button.grid(row=1, column=0, columnspan=2, padx=10, pady=10)

    center_window(search_window)


def resize_image(new_width=None, new_height=None):
    """根据窗口大小调整图片大小"""
    if new_width is None or new_height is None:
        new_width = root.winfo_width()
        new_height = root.winfo_height()
    image = copy_of_image.resize((new_width, new_height))
    photo = ImageTk.PhotoImage(image)
    background_label.config(image=photo)
    background_label.image = photo


# 居中显示窗口
def center_window(window):
    window.update_idletasks()
    screen_width = window.winfo_screenwidth()
    screen_height = window.winfo_screenheight()
    window_width = window.winfo_width()
    window_height = window.winfo_height()
    x = (screen_width - window_width) // 2
    y = (screen_height - window_height) // 2
    window.geometry('{}x{}+{}+{}'.format(window_width, window_height, x, y))


# 登录界面
# 初始化Tkinter窗口
root = tk.Tk()
root.title("学生信息管理系统 - 登录")


# 放大登录页面尺寸一倍
width = 300  # 修改宽度为原来的两倍
height = 200  # 修改高度为原来的两倍
screen_width = root.winfo_screenwidth()
screen_height = root.winfo_screenheight()
x = (screen_width - width) // 2
y = (screen_height - height) // 2

root.geometry('{}x{}+{}+{}'.format(width, height, x, y))


# 图片路径，请替换为你的图片路径
background_image_path = "image.jpg"
# 使用PIL加载背景图片
image = Image.open(background_image_path)
copy_of_image = image.copy()  # 保留原始图像的副本，避免Tkinter垃圾回收问题
photo = ImageTk.PhotoImage(image)

# 创建一个Label放置背景图片，并绑定事件以便窗口调整大小时图片也能随之调整
background_label = tk.Label(root, image=photo)
background_label.place(x=0, y=0, relwidth=1, relheight=1)
root.bind("<Configure>", resize_image)
# root.update_idletasks()  # 确保窗口大小已被设置
# 手动调用resize_image函数以在程序启动时铺满窗口
resize_image()


# 设置登录账户密码框居中并有合适的间距
tk.Label(root, text="用户名").grid(row=0, column=0, padx=10, pady=5)
tk.Label(root, text="密码").grid(row=1, column=0, padx=10, pady=5)

username_entry = tk.Entry(root)
password_entry = tk.Entry(root, show="*")

username_entry.grid(row=0, column=1, padx=10, pady=5)
password_entry.grid(row=1, column=1, padx=10, pady=5)

tk.Button(root, text="登录", command=login).grid(row=2, column=1, padx=10, pady=5)
tk.Button(root, text="注册", command=register).grid(row=2, column=0, padx=10, pady=5)

root.mainloop()